/****************************************************************************
 *
 * Copyright (C) 2003-2008 Los Alamos National Security, LLC
 *                         Packet Analytics Corporation
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License Version 2 as
 * published by the Free Software Foundation.  You may not use, modify or
 * distribute this program under any other version of the GNU General
 * Public License.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
 *
 ****************************************************************************/
package nfse.gui.server;

import nfse.NetFSE;
import nfse.NetFSERecord;
import nfse.NetFSEUtil;
import nfse.gui.client.QueryService;
import nfse.gui.query.*;
import nfse.gui.record.*;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class QueryServiceImpl extends NetFSEServiceServlet implements QueryService {

    public QueryServiceImpl() {
        super();

        try {
            // The server may have restarted with queries in progress, set their
            // status to -1 to indicate a crashed search that will need to be
            // rerun.
            Statement s = createResultStatement();
            s.execute("update queries set status = -1 where status = 0");
            s.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private String convertFromHex(String line) {
        byte[] bytes = new byte[line.length() / 2];
        for (int i = 0; i < bytes.length; i++)
            bytes[i] = (byte) Integer.parseInt(line.substring(i * 2, i * 2 + 2), 16);
        return new String(bytes);
    }

    public void delete(String queryID) {
        try {

            if (ServerUtil.IS_TEST_DRIVE && !isAdmin(getUserName())) {
                return;// "This feature disabled for the test drive.";
            }

            Statement s1 = createResultStatement();

            ResultSet rs = s1.executeQuery("select * from Queries where Query_ID='" + queryID + "'");
            if (!rs.next()) {
                rs.close();
                s1.close();
                // System.out.println("Invalid query ID specified: " + queryID);
                return;
            } else {
                int status = rs.getInt("Status");
                if (status == 2) {
                    rs.close();
                    s1.close();
                    // System.out.println("Cannot delete a query being
                    // merged.");
                    return;
                }
            }
            rs.close();

            int[] types = NetFSE.getTypes();
            for (int i = 0; i < types.length; i++) {
                try {
                    s1.execute("drop table " + queryID + "_" + types[i]);
                } catch (Exception E) {
                }
            }
            try {
                s1.execute("drop table " + queryID);
            } catch (Exception E) {
            }
            try {
                s1.execute("delete from Queries where Query_ID='" + queryID + "'");
            } catch (Exception E) {
            }
            try {
                s1.execute("delete from Query_Types where Query_ID='" + queryID + "'");
            } catch (Exception E) {
            }

            s1.close();
            // c.close();
        } catch (Exception E) {
            E.printStackTrace();
        }
    }

    public int delete(String queryID, int type, String deleteSQL) {
        try {

            if (ServerUtil.IS_TEST_DRIVE && !isAdmin(getUserName())) {
                return 0;// "This feature disabled for the test drive.";
            }

            Statement s1 = createResultStatement();

            ResultSet rs = s1.executeQuery("select * from Queries where Query_ID='" + queryID + "'");
            rs.next();
            int status = rs.getInt("Status");
            rs.close();

            s1.execute("delete from " + queryID + "_" + type + " where " + deleteSQL);

            rs = s1.executeQuery("select count(*) as a from " + queryID + "_" + type);
            rs.next();
            int count2 = rs.getInt("a");
            rs.close();

            if (status == 1) {
                String sql = "update Query_Types set Count=" + count2 + " where Query_ID='" + queryID
                        + "' and Type_ID=" + type + "";
                // System.out.println(sql);
                s1.execute(sql);
            }
            s1.close();
            return count2;
        } catch (Exception E) {
            E.printStackTrace();
        }
        return 0;
    }

    public void deleteQueryOptions(String queryID) {
        try {

            if (ServerUtil.IS_TEST_DRIVE && !isAdmin(getUserName())) {
                return;// "This feature disabled for the test drive.";
            }

            Statement s1 = createResultStatement();

            try {
                s1.execute("update Query_Options set Deleted=1 where Query_ID='" + queryID + "'");
            } catch (Exception E) {
                E.printStackTrace();
            }

            s1.close();

        } catch (Exception E) {
            E.printStackTrace();
        }
    }

    public QueryResult get(String queryID, int n, int offset, int type, String where, String orderBy) {
        String sql = "";
        GUIRecordFactory recGen = null;
        NetFSERecord tempRec = null;
        try {

            Statement s1 = createResultStatement();
            Statement s2 = createResultStatement();
            Statement s3 = createResultStatement();

            QueryResult qr = new QueryResult();
            qr.count = -1;
            // qr.records = new Record[0];
            qr.flexRecordGroup = new FlexRecordGroup();

            Vector<FlexRecord> records = new Vector<FlexRecord>();
            String table = queryID + "_" + type;

            NetFSERecord rec = null;
            tempRec = (NetFSERecord) NetFSE.getTypeClass(type);
            ResultSet rs1 = null;

            sql = "select * from " + queryID + "_" + type + " limit 1";
            try {
                rs1 = s1.executeQuery(sql);
                rs1.close();
            } catch (Exception e) {
                if (rs1 != null)
                    rs1.close();
                s1.close();
                s2.close();
                s3.close();
                return null;
            }

            sql = "select * from Queries where Query_ID='" + queryID + "'";
            rs1 = s1.executeQuery(sql);
            if (rs1.next()) {

                Timestamp now = new Timestamp(System.currentTimeMillis());
                sql = "select * from Queries where Query_ID='" + queryID + "' and TS is not null";
                ResultSet rs2 = s2.executeQuery(sql);
                if (rs2.next()) {
                    rs2.close();
                    try {
                        sql = "update Queries set TS='" + now + "' where Query_ID='" + queryID + "'";
                        s2.execute(sql);
                    } catch (Exception E) {
                        E.printStackTrace();
                    }
                }

                int status = rs1.getInt("Status");
                int count = -1;
                if (where.length() == 0) {
                    if (status == 1) {
                        rs1.close();
                        sql = "select * from Query_Types where Query_ID='" + queryID + "' and Type_ID=" + type;
                        rs1 = s1.executeQuery(sql);
                        if (rs1.next())
                            count = rs1.getInt("Count");
                    } else if (status == 0) {
                        rs1.close();
                        sql = "select count(*) as a from " + table + "";
                        try {
                            rs1 = s1.executeQuery(sql);
                            if (rs1.next())
                                count = rs1.getInt("a");
                        } catch (Exception e) {
                            s1.close();
                            s2.close();
                            s3.close();
                            return qr;
                        }
                    }
                } else {
                    int idx = where.toUpperCase().indexOf("WHERE");
                    if (idx >= 0)
                        where = where.substring(idx + 6);
                    sql = "select count(*) as a from " + queryID + ", " + table + " where Type_ID=" + type + " and "
                            + queryID + ".ID=" + queryID + "_" + type + ".ID ";
                    if (where.length() > 0) {
                        sql += " and (" + where + ")";
                    }
                    rs1 = s1.executeQuery(sql);
                    rs1.next();
                    count = rs1.getInt("a");

                }
                // System.out.println(sql);
                qr.count = count;

                rs1.close();

                if (offset < 0)
                    offset = 0;

                int last = offset + n;
                if (last > count)
                    last = count;

                String record = "";
                String recordHex = "";
                String line = "";

                int sensor = -1;

                sql = "select * from " + queryID + ", " + table + " where Type_ID=" + type + " and " + queryID + ".ID="
                        + queryID + "_" + type + ".ID ";
                if (where.length() > 0) {
                    sql += " and (" + where + ") ";
                }
                if (orderBy.length() > 0) {
                    sql += " order by " + orderBy;
                }
                sql += " limit " + n + " offset " + offset;

                // System.out.println(sql);

                try {
                    rs1 = s1.executeQuery(sql);
                } catch (Exception e) {
                    // System.out.println(sql);
                    // e.printStackTrace();

                    s1.close();
                    s2.close();
                    s3.close();
                    // c.close();
                    return null;
                }

                recGen = (GUIRecordFactory) NetFSE.getRecordFactory(type);
                FlexRecordField[] fields = recGen.getFlexRecordFields();
                while (rs1.next()) {
                    sensor = rs1.getInt("Sensor_ID");

                    record = rs1.getString("Record");
                    recordHex = null;
                    try {
                        recordHex = rs1.getString("Record_Hex");
                    } catch (Exception e) {
                        // e.printStackTrace();
                    }

                    line = null;
                    if (recordHex != null)
                        line = convertFromHex(recordHex);
                    else {
                        try {
                            line = convertFromHex(record);
                        } catch (Exception e) {
                            line = record;
                        }
                    }

                    try {
                        rec = (NetFSERecord) recGen.generate(line, sensor);
                    } catch (Exception e) {
                        e.printStackTrace();
                        rec = null;
                    }

                    if (rec != null) {
                        FlexRecord flexRecord = recGen.getFlexRecord(rec);
                        if (flexRecord.specialValues == null)
                            GUIRecordFactory.setSpecialValues(flexRecord, fields);
                        records.addElement(flexRecord);
                    }
                }
                rs1.close();

            } else {
                rs1.close();
            }

            rs1 = s1.executeQuery("select * from Queries where Query_ID='" + queryID + "'");
            QuerySummary qs = new QuerySummary();
            if (rs1.next()) {

                qs.queryID = rs1.getString("Query_ID");
                qs.status = rs1.getInt("Status");
                qs.count = rs1.getInt("Count");
                qs.ts = rs1.getString("TS");
                Timestamp ts = rs1.getTimestamp("TS");
                if (ts != null) {
                    ts = new Timestamp(ts.getTime() + 172800000);
                    if (!qs.ts.equals("0000-00-00 00:00:00.0"))
                        qs.ts = ts.toString();
                } else {
                    qs.ts = "Permanent";
                }
                qs.note = rs1.getString("Note");
                // String[] typeStrs = new String[types.length];
                ResultSet rs2 = s2.executeQuery("select * from Query_Types where Query_ID='" + qs.queryID + "'");
                while (rs2.next()) {
                    int tempType = rs2.getInt("Type_ID");
                    int count2 = rs2.getInt("Count");
                    if (qs.status == 0) {
                        try {
                            ResultSet rs3 = s3.executeQuery("select count(*) as a from " + qs.queryID + "_" + tempType);
                            rs3.next();
                            count2 = rs3.getInt("a");
                            rs3.close();
                        } catch (Exception e) {

                        }
                    }
                    qs.setTypeCount(tempType, count2);
                }

                rs2.close();

            }

            s1.close();
            s2.close();
            s3.close();
            // c.close();

            FlexRecord[] temp = new FlexRecord[records.size()];
            for (int i = 0; i < temp.length; i++)
                temp[i] = (FlexRecord) records.elementAt(i);

            qr.flexRecordGroup = new FlexRecordGroup();
            qr.flexRecordGroup.typeID = type;
            if (tempRec != null) {
                qr.flexRecordGroup.flexRecordFields = recGen.getFlexRecordFields();
                qr.schema = recGen.getCreateFieldTableSQL(tempRec.getTypeName());
                // System.out.println(qr.schema);
            } else {
                qr.flexRecordGroup.flexRecordFields = new FlexRecordField[0];
            }
            qr.flexRecordGroup.records = temp;
            qr.qs = qs;

            return qr;
        } catch (Exception E) {
            E.printStackTrace();
        }
        return null;
    }

    /***************************************************************************
     * Method to translate error codes from QuerySocket into human-readable
     * error messages.
     * 
     * @param reply
     *            The error message returned by the QuerySocket connection
     * @return
     */
    public String getError(int reply) {
        String temp = "Unknown error";
        switch (reply) {
        case -1:
            temp = "Query parse error";
            break;
        case -2:
            temp = "MetaDatabase connection error";
            break;
        case -3:
            temp = "User authentication error";
            break;
        case -4:
            temp = "Invalid query error";
            break;
        case -5:
            temp = "Empty query error";
            break;

        }
        return temp;
    }

    public QueryInfo getQueryInfo(String queryID) {
        try {
            QueryInfo info = new QueryInfo();
            Statement statement = null;
            try {
                statement = resultConnection.createStatement();
            } catch (Exception e) {
                e.printStackTrace();
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                resultConnection = DriverManager.getConnection("jdbc:mysql://" + NetFSE.RESULT_DB_HOST + "/"
                        + NetFSE.RESULT_DB_DATABASE, NetFSE.RESULT_DB_USER, NetFSE.RESULT_DB_PASSWORD);
                statement = resultConnection.createStatement();
            }

            String sql = "select Note from Queries where Query_ID='" + queryID + "'";
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                info.note = NetFSEUtil.convertFromHex(rs.getString("Note"));
                if (info.note == null)
                    info.note = "";
            }
            rs.close();

            Vector<Integer> types = new Vector<Integer>();
            sql = "select * from Query_Types where Query_ID='" + queryID + "'";
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                types.addElement(new Integer(rs.getInt("type_id")));
            }
            rs.close();

            info.types = new int[types.size()];
            for (int i = 0; i < info.types.length; i++)
                info.types[i] = types.elementAt(i);

            statement.close();
            return info;
            // c.close();
        } catch (Exception E) {
            E.printStackTrace();
        }
        return null;
    }

    public String getUserName() {
        try {
            String userName = this.getThreadLocalRequest().getRemoteUser();
            if (userName == null || userName.length() == 0)
                userName = "";
            return userName;
        } catch (Exception e) {
            return "";
        }

    }

    private boolean isAdmin(String user) {
        try {
            Statement statement = createMetadataStatement();
            boolean admin = false;
            String userName = getUserName();

            ResultSet rs = statement.executeQuery("select * from Users where User_Name='" + userName + "'");
            if (rs.next()) {
                if (rs.getInt("Admin") != 0) {
                    admin = true;
                }
            }
            rs.close();
            statement.close();
            return admin;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public void makePermanent(String queryID) {
        try {

            if (ServerUtil.IS_TEST_DRIVE && !isAdmin(getUserName())) {
                return;// "This feature disabled for the test drive.";
            }

            Statement s1 = createResultStatement();

            // System.out.println("Make permanent: " + queryID);

            String sql = "update Queries set TS = null where Query_ID='" + queryID + "'";
            // System.out.println(sql);
            s1.execute(sql);

            s1.close();
            // c.close();
        } catch (Exception E) {
            E.printStackTrace();
        }
    }

    public void multiDelete(String[] queryIDs) {

        for (int i = 0; i < queryIDs.length; i++) {
            delete(queryIDs[i]);
        }
    }

    public void multiMakePermanent(String[] queryIDs) {
        for (int i = 0; i < queryIDs.length; i++) {
            makePermanent(queryIDs[i]);
        }
    }

    public void multiQueryHistoryDelete(String[] queryIDs) {
        for (int i = 0; i < queryIDs.length; i++) {
            deleteQueryOptions(queryIDs[i]);
        }
    }

    public QuerySummary[] myQueries() {
        QuerySummary[] results = null;
        try {

            Statement s1 = createResultStatement();
            Statement s2 = createResultStatement();
            Statement s3 = createResultStatement();

            Vector<QuerySummary> temp = new Vector<QuerySummary>();
            String userName = getUserName();

            String sql = "select * from Queries where (User_ID='" + userName + "' or User_ID='') order by TS desc";
            // System.out.println(sql);
            ResultSet rs1 = s1.executeQuery(sql);

            // ResultSet rs1 = s1.executeQuery("select * from Queries ");
            while (rs1.next()) {
                QuerySummary qs = new QuerySummary();
                qs.queryID = rs1.getString("Query_ID");
                qs.status = rs1.getInt("Status");
                qs.count = rs1.getInt("Count");
                qs.ts = rs1.getString("TS");
                Timestamp ts = rs1.getTimestamp("TS");
                if (qs.ts != null && qs.ts.equals("0000-00-00 00:00:00.0")) {
                    qs.ts = "Never";
                } else if (ts != null) {
                    ts = new Timestamp(ts.getTime() + ServerUtil.EXPIRE_HOURS * 60 * 60 * 1000);
                    if (!qs.ts.equals("0000-00-00 00:00:00.0"))
                        qs.ts = ts.toString();
                } else {
                    qs.ts = "Never";
                }
                qs.note = NetFSEUtil.convertFromHex(rs1.getString("Note"));
                ResultSet rs2 = s2.executeQuery("select * from Query_Types where Query_ID='" + qs.queryID + "'");
                while (rs2.next()) {
                    int type = rs2.getInt("Type_ID");
                    int count2 = rs2.getInt("Count");
                    if (qs.status == 0) {
                        try {
                            ResultSet rs3 = s3.executeQuery("select count(*)  as a from " + qs.queryID + "_" + type);
                            rs3.next();
                            count2 = rs3.getInt("a");
                            rs3.close();
                        } catch (Exception e) {
                            // e.printStackTrace();
                        }
                    }
                    qs.setTypeCount(type, count2);
                }

                rs2.close();
                temp.addElement(qs);
            }

            rs1.close();
            s1.close();
            s2.close();
            s3.close();
            // c.close();

            results = new QuerySummary[temp.size()];
            for (int i = 0; i < results.length; i++) {
                results[i] = (QuerySummary) temp.elementAt(i);
            }

        } catch (Exception E) {
            this.getServletContext().log(E.getMessage(), E);
            E.printStackTrace();
        }
        return results;
    }

    public String query(String query, String note, int[] types, int limit, int total) {
        try {
            Statement s1 = createResultStatement();

            String userName = getUserName();

            if (note == null)
                note = "";
            if (note.length() > 0) {
                try {
                    NetFSEUtil.convertFromHex(note);

                } catch (Exception e) {
                    note = NetFSEUtil.convertToHex(note);
                }
            }

            Vector<String> tempQueries = new Vector<String>();
            String queryHead = query.substring(0, query.indexOf(">") + 1);
            String queryBody = query.substring(query.indexOf(">") + 1);
            for (int i = 0; i < types.length; i++) {
                try {
                    // tempTypes2.addElement(new Integer(tempTypes[i].trim()));

                    tempQueries.addElement(queryHead + "<Data type=\"" + types[i] + "\"/>" + queryBody);
                    // out.println(query);
                } catch (Exception E) {
                    // no op
                }
            }
            // query += queryBody;

            String[] queries = new String[tempQueries.size()];
            for (int i = 0; i < queries.length; i++)
                queries[i] = tempQueries.elementAt(i);

            String queryID = "";

            ResultSet rs1 = s1.executeQuery("select nextval('query_id')");
            rs1.next();
            queryID = "s" + rs1.getInt(1);
            rs1.close();

            String resultTableSQL = "create table " + queryID + " (ID bigint, Type_ID int, Sensor_ID int, Record text)";
            try {

                s1.execute(resultTableSQL);
                // worked = true;
                String indexSQL = "create index " + queryID + "_id_idx on " + queryID + " (ID)";
                s1.execute(indexSQL);
                indexSQL = "create index " + queryID + "_type_idx on " + queryID + " (Type_ID)";
                s1.execute(indexSQL);
                indexSQL = "create index " + queryID + "_sensor_idx on " + queryID + " (Sensor_ID)";
                s1.execute(indexSQL);
            } catch (Exception E) {
                // System.out.println(resultTableSQL);
                // E.printStackTrace();
                // qid++;
                Thread.sleep(1000);
            }

            String[] indexSQL = null;
            String fieldTableSQL = "";

            for (int i = 0; i < types.length; i++) {
                // NetForSERecord rec = records[types[i]];
                GUIRecordFactory rec = (GUIRecordFactory) NetFSE.getRecordFactory(types[i]);
                fieldTableSQL = rec.getCreateFieldTableSQL(queryID + "_" + types[i]);
                indexSQL = rec.getIndexFieldTableSQL(queryID + "_" + types[i]);
                // System.out.println(fieldTableSQL);
                s1.execute(fieldTableSQL);
                for (int x = 0; x < indexSQL.length; x++) {
                    try {
                        s1.execute(indexSQL[x]);
                    } catch (Exception e) {
                        // System.out.println(indexSQL[x]);
                        e.printStackTrace();
                    }
                }

            }

            String insertSQL = "";

            try {
                insertSQL = "insert into Queries values ('" + queryID + "',0,0,'" + userName + "','"
                        + new Timestamp(System.currentTimeMillis()) + "','" + note + "')";
                s1.execute(insertSQL);
                for (int i = 0; i < types.length; i++) {
                    insertSQL = "insert into Query_Types values ('" + queryID + "'," + types[i] + ",0)";
                    s1.execute(insertSQL);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

             QueryThread qt = new QueryThread(queryID, queries, types, s1,
             total, limit);
           //QueryThreadCopy qt = new QueryThreadCopy(queryID, queries, types, this.resultConnection, total, limit);
            qt.start();

            return queryID;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "failed";

    }

    public QueryOptions[] queryHistory() {
        QueryOptions[] results = null;
        try {
            Statement s1 = createResultStatement();

            Vector<QueryOptions> temp = new Vector<QueryOptions>();
            String userName = getUserName();

            // Statement s2 = c.createStatement();
            ResultSet rs1 = s1.executeQuery("select * from Query_Options where (User_ID='" + userName
                    + "' or User_ID='') and Deleted=0 order by TS desc");
            // ResultSet rs1 = s1.executeQuery("select * from Queries ");
            while (rs1.next()) {

                QueryOptions qo = new QueryOptions();
                qo.queryID = rs1.getString("Query_ID");
                qo.queryType = rs1.getInt("Query_Type");
                qo.ts = rs1.getString("TS");
                Timestamp startTS = rs1.getTimestamp("Start_TS");
                if (startTS != null)
                    qo.startDate = new Date(startTS.getTime());
                Timestamp endTS = rs1.getTimestamp("End_TS");
                if (endTS != null)
                    qo.endDate = new Date(endTS.getTime());
                qo.srcIP1 = rs1.getString("Src_IP_1");
                qo.srcIP2 = rs1.getString("Src_IP_2");
                qo.dstIP1 = rs1.getString("Dst_IP_1");
                qo.dstIP2 = rs1.getString("Dst_IP_2");
                qo.dstPort1 = rs1.getInt("Dst_Port_1");
                qo.dstPort2 = rs1.getInt("Dst_Port_2");
                qo.dstIPPred = rs1.getInt("Dst_IP_Pred");
                qo.srcIPPred = rs1.getInt("Src_IP_Pred");
                qo.dstPortPred = rs1.getInt("Dst_Port_Pred");
                qo.ips = rs1.getString("IPs");
                qo.ipSearchType = rs1.getInt("IP_Search_Type");
                // qo.note = ServerUtil.convertFromHex(rs1.getString("Note"));
                qo.note = "";
                qo.limit = rs1.getInt("Type_Limit");
                String[] typeStrs = rs1.getString("Types").split(";");
                // int[] types = new int[typeStrs.length];
                for (int i = 0; i < typeStrs.length; i++) {
                    if (typeStrs[i].length() > 0)
                        qo.setType(Integer.parseInt(typeStrs[i]), true);
                }
                temp.addElement(qo);
            }

            rs1.close();
            s1.close();
            // s2.close();
            // c.close();

            results = new QueryOptions[temp.size()];
            for (int i = 0; i < results.length; i++) {
                results[i] = temp.elementAt(i);
            }
        } catch (Exception E) {
            E.printStackTrace();
        }
        return results;
    }

    public QueryOptions getSearchQuery(String queryId) {
        QueryOptions qo = null;
        new QueryOptions();
        try {
            Statement s1 = createResultStatement();

            ResultSet rs1 = s1.executeQuery("select * from Query_Options where query_id =  \'" + queryId + "\'");
            if (rs1.next()) {
                qo = new QueryOptions();
                qo.queryID = rs1.getString("Query_ID");
                qo.queryType = rs1.getInt("Query_Type");
                qo.ts = rs1.getString("TS");
                qo.startDate = new Date(rs1.getTimestamp("Start_TS").getTime());
                qo.endDate = new Date(rs1.getTimestamp("End_TS").getTime());
                qo.srcIP1 = rs1.getString("Src_IP_1");
                qo.srcIP2 = rs1.getString("Src_IP_2");
                qo.dstIP1 = rs1.getString("Dst_IP_1");
                qo.dstIP2 = rs1.getString("Dst_IP_2");
                qo.dstPort1 = rs1.getInt("Dst_Port_1");
                qo.dstPort2 = rs1.getInt("Dst_Port_2");
                qo.dstIPPred = rs1.getInt("Dst_IP_Pred");
                qo.srcIPPred = rs1.getInt("Src_IP_Pred");
                qo.dstPortPred = rs1.getInt("Dst_Port_Pred");
                qo.ips = rs1.getString("IPs");
                qo.ipSearchType = rs1.getInt("IP_Search_Type");
                // qo.note = ServerUtil.convertFromHex(rs1.getString("Note"));
                qo.note = "";
                qo.limit = rs1.getInt("Type_Limit");
                String[] typeStrs = rs1.getString("Types").split(";");
                for (int i = 0; i < typeStrs.length; i++) {
                    // System.out.println("TYPE: " + typeStrs[i]);
                    if (typeStrs[i].length() > 0)
                        qo.setType(Integer.parseInt(typeStrs[i]), true);
                }
            }

            rs1.close();
            s1.close();

        } catch (Exception E) {
            E.printStackTrace();
        }
        return qo;
    }

    public void saveQueryOptions(QueryOptions options) {
        try {

            Statement s1 = createResultStatement();

            String userName = getUserName();

            options.note = encodeNote(options.note);

            if (options.queryID == null)
                options.queryID = userName;

            // System.out.println("Save Query Options: " + options.queryID);

            String sql = "insert into Query_Options values (";
            sql += "'" + options.queryID + "'";
            sql += ",";
            sql += "" + options.queryType + "";
            sql += ",";
            sql += "'" + new Timestamp(options.startDate.getTime()) + "'";
            sql += ",";
            sql += "'" + new Timestamp(options.endDate.getTime()) + "'";
            sql += ",";
            sql += "'" + options.srcIP1 + "'";
            sql += ",";
            sql += "'" + options.srcIP2 + "'";
            sql += ",";
            sql += "" + options.srcIPPred + "";
            sql += ",";
            sql += "'" + options.dstIP1 + "'";
            sql += ",";
            sql += "'" + options.dstIP2 + "'";
            sql += ",";
            sql += "" + options.dstIPPred + "";
            sql += ",";
            sql += "" + options.dstPort1 + "";
            sql += ",";
            sql += "" + options.dstPort2 + "";
            sql += ",";
            sql += "" + options.dstPortPred + "";
            sql += ",";
            sql += "''";
            sql += ",";
            sql += "''";
            sql += ",";
            sql += "0";
            sql += ",";
            sql += "'" + options.ips + "'";
            sql += ",";
            sql += "" + options.ipSearchType + "";
            sql += ",";
            sql += "'" + options.note + "'";
            sql += ",";
            sql += "'" + userName + "'";
            sql += ",";
            sql += "'" + (new Timestamp(System.currentTimeMillis())).toString() + "'";
            sql += ",";
            int[] types = options.getTypes();
            String typeStr = "";
            for (int i = 0; i < types.length; i++) {
                if (i > 0)
                    typeStr += ";";
                typeStr += "" + types[i];
            }
            sql += "'" + typeStr + "'";
            sql += ",";
            sql += "" + options.limit + "";
            sql += ",0";
            sql += ")";

            s1.execute(sql);

            s1.close();
        } catch (Exception E) {
            E.printStackTrace();
        }
    }

    private String encodeNote(String note) {
        if (note == null)
            note = "";
        if (note.length() > 0) {
            try {
                NetFSEUtil.convertFromHex(note);
            } catch (Exception e) {
                note = NetFSEUtil.convertToHex(note);
            }
        }
        return note;
    }

    public void setNote(String queryID, String note) {
        try {

            Statement s1 = createResultStatement();

            String sql = "update Queries set Note='" + encodeNote(note) + "' where Query_ID='" + queryID + "'";
            s1.execute(sql);

            s1.close();
            // c.close();
        } catch (Exception E) {
            E.printStackTrace();
        }
    }

    public QueryResult timeline(String queryID, int n, int[] types, String[] where) {
        try {
            QueryResult qr = new QueryResult();

            QueryResult[] qrs = new QueryResult[types.length];
            int[] startTSIdxs = new int[types.length];
            int[] srcipIdxs = new int[types.length];
            int[] dstipIdxs = new int[types.length];
            int[] srcportIdxs = new int[types.length];
            int[] dstportIdxs = new int[types.length];
            int[] protocolIdxs = new int[types.length];
            int[] recordIdxs = new int[types.length];
            for (int i = 0; i < types.length; i++) {
                qrs[i] = get(queryID, n, 0, types[i], where[i], "startts asc");
                startTSIdxs[i] = -1;
                srcipIdxs[i] = -1;
                dstipIdxs[i] = -1;
                srcportIdxs[i] = -1;
                dstportIdxs[i] = -1;
                protocolIdxs[i] = -1;
                recordIdxs[i] = 0;
                if (qrs[i] != null && qrs[i].flexRecordGroup != null) {
                    startTSIdxs[i] = qrs[i].flexRecordGroup.getFieldIdx("startts");
                    srcipIdxs[i] = qrs[i].flexRecordGroup.getFieldIdx("srcip");
                    dstipIdxs[i] = qrs[i].flexRecordGroup.getFieldIdx("dstip");
                    dstportIdxs[i] = qrs[i].flexRecordGroup.getFieldIdx("dstport");
                    srcportIdxs[i] = qrs[i].flexRecordGroup.getFieldIdx("srcport");
                    protocolIdxs[i] = qrs[i].flexRecordGroup.getFieldIdx("protocol");
                }
            }
            Vector<FlexRecord> records = new Vector<FlexRecord>();
            int idx = 0;
            while (idx < n) {
                Timestamp minTS = null;
                int minIdx = -1;
                for (int x = 0; x < qrs.length; x++) {
                    if (qrs[x] != null && qrs[x].flexRecordGroup != null) {
                        FlexRecord rec = qrs[x].flexRecordGroup.getRecord(recordIdxs[x]);
                        if (rec != null && startTSIdxs[x] != -1) {
                            rec.startTSIdx = startTSIdxs[x];
                            rec.dstipIdx = dstipIdxs[x];
                            rec.srcipIdx = srcipIdxs[x];
                            rec.srcportIdx = srcportIdxs[x];
                            rec.dstportIdx = dstportIdxs[x];
                            rec.protoIdx = protocolIdxs[x];
                            String tsStr = rec.values[startTSIdxs[x]];
                            Timestamp ts = Timestamp.valueOf(tsStr);
                            if (minTS == null || ts.before(minTS)) {
                                minTS = ts;
                                minIdx = x;
                            }
                        }
                    }
                }
                if (minTS == null) {
                    idx = n;
                } else {

                    FlexRecord rec = qrs[minIdx].flexRecordGroup.getRecord(recordIdxs[minIdx]);
                    rec.colNames = qrs[minIdx].flexRecordGroup.getColumnNames();
                    if (rec.values.length > qr.maxCol)
                        qr.maxCol = rec.values.length;
                    recordIdxs[minIdx]++;
                    records.addElement(rec);
                    // System.out.println(rec.toString());
                }
                idx++;
            }
            qr.flexRecordGroup = new FlexRecordGroup();
            FlexRecord[] recs = new FlexRecord[records.size()];
            for (int i = 0; i < recs.length; i++) {
                recs[i] = records.elementAt(i);
            }
            qr.flexRecordGroup.records = recs;
            return qr;
        } catch (Exception E) {
            E.printStackTrace();
        }
        return null;
    }

    public String merge(String[] queryIDs) {
        // TODO Auto-generated method stub
        return null;
    }

}
